﻿namespace zyn_hy_login
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class DB_conn
    {
        private string connString;
        private string dbTable;
        private string folderImg;
        private string img;
        private string mailatt;
        private string mailcontent;
        private string mailtitle;
        private string proEdit;
        private string proNew;
        private string proRead;
        private string tableName;
        private string tableType;

        public DB_conn()
        {
        }

        public DB_conn(string strConnId)
        {
            DataRow row = DBCommon.getDataBySql("select * from d_a where id = " + strConnId).Tables[0].Rows[0];
            this.connString = "";
            this.connString = this.connString + "server=" + row["x1"].ToString().Trim();
            this.connString = this.connString + ";database=" + row["x2"].ToString().Trim();
            this.connString = this.connString + ";User ID=" + row["x3"].ToString().Trim();
            this.connString = this.connString + ";Password=" + row["x4"].ToString().Trim();
            this.dbTable = row["x5"].ToString().Trim();
            this.img = row["x7"].ToString().Trim();
            this.tableName = row["name"].ToString().Trim();
            this.tableType = row["x6"].ToString().Trim();
            this.folderImg = row["x8"].ToString().Trim();
            this.proNew = row["x9"].ToString().Trim();
            this.proEdit = row["x10"].ToString().Trim();
            this.proRead = row["x11"].ToString().Trim();
            this.mailtitle = row["x13"].ToString().Trim();
            this.mailcontent = row["x14"].ToString().Trim();
            this.mailatt = row["x15"].ToString().Trim();
        }

        public void CreateConnString(string strConnId)
        {
            DataRow row = DBCommon.getDataBySql("select * from d_a where id = " + strConnId).Tables[0].Rows[0];
            this.connString = "";
            this.connString = this.connString + "server=" + row["x1"].ToString().Trim();
            this.connString = this.connString + ";database=" + row["x2"].ToString().Trim();
            this.connString = this.connString + ";User ID=" + row["x3"].ToString().Trim();
            this.connString = this.connString + ";Password=" + row["x4"].ToString().Trim();
            this.dbTable = row["x5"].ToString().Trim();
            this.img = row["x7"].ToString().Trim();
            this.tableName = row["name"].ToString().Trim();
            this.tableType = row["x6"].ToString().Trim();
            this.folderImg = row["x8"].ToString().Trim();
            this.proNew = row["x9"].ToString().Trim();
            this.proEdit = row["x10"].ToString().Trim();
            this.proRead = row["x11"].ToString().Trim();
            this.mailtitle = row["x13"].ToString().Trim();
            this.mailcontent = row["x14"].ToString().Trim();
            this.mailatt = row["x15"].ToString().Trim();
        }

        public void excuteParamSql(string strSql, SqlParameter[] sqlparams)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = this.connString;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = strSql;
            command.Connection = connection;
            foreach (SqlParameter parameter in sqlparams)
            {
                command.Parameters.Add(parameter);
            }
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void excuteSql(string strSql)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = this.connString;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = strSql;
            command.Connection = connection;
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public int excuteSqlCount(string strSql)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = this.connString;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = strSql;
            command.Connection = connection;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            int count = dataSet.Tables[0].Rows.Count;
            dataSet.Clear();
            connection.Close();
            return count;
        }

        public DataSet getDataBySql(string strSql)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = this.connString;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = strSql;
            command.Connection = connection;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            return dataSet;
        }

        public SqlDataReader getDataReadBySql(string strSql)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = this.connString;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = strSql;
            command.Connection = connection;
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public int getMaxId()
        {
            string strSql = "select isnull(max(id),0) from " + this.dbTable;
            DataSet set = this.getDataBySql(strSql);
            if (set.Tables[0].Rows.Count == 0)
            {
                return 0;
            }
            return (int) set.Tables[0].Rows[0][0];
        }

        public int getNameCount(string pid, string cname, string ztType, string ztValue)
        {
            string strSql = " select id from " + this.DbTable + " where name='" + cname + "'";
            if (ztType == "1")
            {
                strSql = strSql + " and zt='" + ztValue + "' ";
            }
            else if (ztType == "2")
            {
                strSql = strSql + " and zt<>'" + ztValue + "' ";
            }
            strSql = strSql + " and id2=" + pid;
            return this.excuteSqlCount(strSql);
        }

        public bool TestConn()
        {
            try
            {
                SqlConnection connection = new SqlConnection(this.connString);
                connection.Open();
                connection.Close();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public string ConnString
        {
            get
            {
                return this.connString;
            }
            set
            {
                this.connString = value;
            }
        }

        public string DbTable
        {
            get
            {
                return this.dbTable;
            }
            set
            {
                this.dbTable = value;
            }
        }

        public string FolderImg
        {
            get
            {
                return this.folderImg;
            }
            set
            {
                this.folderImg = value;
            }
        }

        public string Img
        {
            get
            {
                return this.img;
            }
        }

        public string Mailatt
        {
            get
            {
                return this.mailatt;
            }
            set
            {
                this.mailatt = value;
            }
        }

        public string Mailcontent
        {
            get
            {
                return this.mailcontent;
            }
            set
            {
                this.mailcontent = value;
            }
        }

        public string Mailtitle
        {
            get
            {
                return this.mailtitle;
            }
            set
            {
                this.mailtitle = value;
            }
        }

        public string ProEdit
        {
            get
            {
                return this.proEdit;
            }
            set
            {
                this.proEdit = value;
            }
        }

        public string ProNew
        {
            get
            {
                return this.proNew;
            }
            set
            {
                this.proNew = value;
            }
        }

        public string ProRead
        {
            get
            {
                return this.proRead;
            }
            set
            {
                this.proRead = value;
            }
        }

        public string TableName
        {
            get
            {
                return this.tableName;
            }
            set
            {
                this.tableName = value;
            }
        }

        public string TableType
        {
            get
            {
                return this.tableType;
            }
            set
            {
                this.tableType = value;
            }
        }
    }
}

